-- **************************************************************************
--    Project Name:   全程时效-桃花岛虚假发货监控明细表
--    Job Name:       jms_dm__dm_sham_send_monitor_detail_dt_v2
--    Author :        季修魁
--    date：          2023/10/20
-- **************************************************************************
-- **************************************************************************

---运单轨迹表
with trace_waybill_step_tab_thd as (
select a.waybill_no                                                                                                    as waybill_no
       ,a.send_station_network_code                                                                                    as send_station_network_code
       ,a.send_station_network_type                                                                                    as send_station_network_type
       ,a.send_station_number                                                                                          as send_station_number
       ,a.send_station_send_later_time                                                                                 as send_station_send_time
       ,a.send_station_arrival_early_time                                                                              as send_station_arrival_time
       ,a.send_center_station_number                                                                                   as send_center_station_number
       ,b.name                                                                                                         as send_station_network_name
       ,b.agent_code                                                                                                   as send_station_agent_code
       ,b.agent_name                                                                                                   as send_station_agent_name
       ,b.city_id                                                                                                      as send_station_city_id
       ,b.city_desc                                                                                                    as send_station_city_desc
       ,b.provider_id                                                                                                  as send_station_provider_id
       ,b.provider_desc                                                                                                as send_station_provider_desc
       ,lead(a.send_station_network_code,1,null) over (partition by a.waybill_no order by a.send_station_number)       as next_station_network_code
       ,lead(a.send_station_send_later_time,1,null) over (partition by a.waybill_no order by a.send_station_number)    as next_station_send_time
       ,lead(a.send_station_arrival_early_time,1,null) over (partition by a.waybill_no order by a.send_station_number) as next_station_arrival_time
       ,lead(a.send_station_network_code,2,null) over (partition by a.waybill_no order by a.send_station_number)       as next_next_station_network_code
       ,lead(a.send_station_send_later_time,2,null) over (partition by a.waybill_no order by a.send_station_number)    as next_next_station_send_time
       ,lead(a.send_station_arrival_early_time,2,null) over (partition by a.waybill_no order by a.send_station_number) as next_next_station_arrival_time
       ,lag( a.send_station_network_code,1,null) over (partition by a.waybill_no order by a.send_station_number)       as last_station_network_code
       ,lag( a.send_station_send_later_time,1,null) over (partition by a.waybill_no order by a.send_station_number)    as last_station_send_time
       ,lag( a.send_station_arrival_early_time,1,null) over (partition by a.waybill_no order by a.send_station_number) as last_station_arrival_time
       ,row_number() over(partition by a.waybill_no order by a.send_station_number )                                   as new_send_station_number
       ,ordersource_code
       ,ordersource_name
       ,dt                                                                                                             as dt
  from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt    a
  left join jms_dim.dim_network_whole_massage           b
    on a.send_station_network_code = b.code
 where dt>=date_add('{{ execution_date | cst_ds }}',-30) and dt<='{{ execution_date | cst_ds }}'
   and ordersource_name in ('桃花岛')
   and send_station_network_type<>'3'
),

tab_delivery as (
    select  t.waybill_no                 as waybill_no
           ,t.scan_site_code             as scan_site_code
           ,t.scan_site                  as scan_site
           ,t.scan_time                  as scan_time
           ,net.name                     as deliver_network_name
           ,net.agent_code               as deliver_agent_code
           ,net.agent_name               as deliver_agent_name
           ,net.city_id                  as deliver_city_id
           ,net.city_desc                as deliver_city_desc
           ,net.provider_id              as deliver_provider_id
           ,net.provider_desc            as deliver_provider_desc
    from (select waybill_no,
                 scan_site_code,
                 scan_site,
                 scan_time,
                 row_number() over(partition by waybill_no order by scan_time) rk
           from jms_dwd.dwd_tab_barscan_deliver_base_dt
          where dt>=date_add('{{ execution_date | cst_ds }}',-30) and dt<='{{ execution_date | cst_ds }}'
         ) t
   left join jms_dim.dim_network_whole_massage    net
     on t.scan_site_code = net.code
  where rk = 1
),
wide_unsign_summary_waybill_tab_thd as (
select  t1.waybill_no                                             as waybill_no
       ,t1.input_time                                             as input_time
       ,t1.input_network_code                                     as input_network_code
       ,t1.taking_early_scantime                                  as taking_early_scantime
       ,t1.taking_early_scansitecode                              as taking_early_scansitecode
       ,t1.warehousing_early_scantime                             as warehousing_early_scantime
       ,t1.warehousing_early_scansitecode                         as warehousing_early_scansitecode
       ,t1.first_network_code                                     as first_network_code
       ,t1.end_network_code                                       as end_network_code
       ,t1.sign_time                                              as sign_time
       ,t1.is_reback                                              as is_reback
       ,coalesce(t1.first_center_network_code,t2.in_to_code)  as first_center_network_code
       ,t1.first_center_arrival_early_time                        as first_center_arrival_early_time
       ,t1.second_center_network_code                             as second_center_network_code
       ,t1.second_center_arrival_early_time                          as second_center_arrival_early_time
       ,t1.end_center_network_code                                as end_center_network_code
       ,t3.scan_site_code                                         as deliver_network_code
       ,t3.deliver_network_name                                   as deliver_network_name
       ,t3.deliver_agent_code                                     as deliver_agent_code
       ,t3.deliver_agent_name                                     as deliver_agent_name
       ,t3.deliver_city_id                                        as deliver_city_id
       ,t3.deliver_city_desc                                      as deliver_city_desc
       ,t3.deliver_provider_id                                    as deliver_provider_id
       ,t3.deliver_provider_desc                                  as deliver_provider_desc
       ,t3.scan_time                                              as deliver_time
       ,t1.ordersource_code
       ,t1.ordersource_name
       ,t1.dt
  from (
select waybill_no                                                                                                                      as waybill_no
       ,input_time                                                                                                                     as input_time
       ,input_network_code                                                                                                             as input_network_code
       ,taking_early_scantime                                                                                                          as taking_early_scantime
       ,taking_early_scansitecode                                                                                                      as taking_early_scansitecode
       ,warehousing_early_scantime                                                                                                     as warehousing_early_scantime
       ,warehousing_early_scansitecode                                                                                                 as warehousing_early_scansitecode
       ,first_network_network_code                                                                                                     as first_network_code
       ,first_center_network_code                                                                                                      as first_center_network_code
       ,first_center_arrival_early_time                                                                                                as first_center_arrival_early_time
       ,second_center_network_code
       ,second_center_arrival_early_time
       ,coalesce(sign_early_scantime,outstore_early_scantime)                                                                          as sign_time
       ,is_reback                                                                                                                      as is_reback
       ,dispatch_network_code                                                                                                          as end_network_code
       ,end_center_network_code                                                                                                        as end_center_network_code
       ,ordersource_code                                                                                                               as ordersource_code
       ,ordersource_name                                                                                                               as ordersource_name
       ,dt                                                                                                                             as dt
  from jms_dwd.dwd_wide_unsign_summary_waybill_dt
 where dt>=date_add('{{ execution_date | cst_ds }}',-30)
   and dt<='{{ execution_date | cst_ds }}'
   and ordersource_name in ('桃花岛')
   and is_reback=0
   and is_intercept=0
   )                                                   t1
  left join (select in_from_code,out_to_code,in_from_name,out_to_name,max(in_to_code)  as in_to_code
               from jms_dm.dm_route_fastest
               where dt = '{{ execution_date | cst_ds }}'
               group by in_from_code,out_to_code,in_from_name,out_to_name
             )                                         t2
   on t1.first_network_code = t2.in_from_code
  and t1.end_network_code=t2.out_to_code
  left join tab_delivery                               t3
    on t1.waybill_no = t3.waybill_no
),

----整合轨迹明细
----insert overwrite table bidefault.waybill_main_info_test_1016 partition (dt)
waybill_main_info_test  as (
select a.waybill_no                                         as waybill_no
      ,a.input_time                                         as input_time
      ,a.input_network_code                                 as input_network_code
      ,a.taking_early_scantime                              as taking_scantime
      ,a.taking_early_scansitecode                          as taking_scansitecode
      ,a.warehousing_early_scantime                         as warehousing_scantime
      ,a.warehousing_early_scansitecode                     as warehousing_scansitecode
      ,a.first_network_code                                 as first_network_code
      ,c.name                                               as first_network_name
      ,c.agent_code                                         as first_agent_code
      ,c.agent_name                                         as first_agent_name
      ,c.city_id                                            as first_city_id
      ,c.city_desc                                          as first_city_desc
      ,c.provider_id                                        as first_provider_id
      ,c.provider_desc                                      as first_provider_desc
      ,a.end_network_code                                   as end_network_code
      ,d.name                                               as end_network_name
      ,d.agent_code                                         as end_agent_code
      ,d.agent_name                                         as end_agent_name
      ,d.city_id                                            as end_city_id
      ,d.city_desc                                          as end_city_desc
      ,d.provider_id                                        as end_provider_id
      ,d.provider_desc                                      as end_provider_desc
      ,a.deliver_network_code                               as deliver_network_code
      ,a.deliver_network_name                               as deliver_network_name
      ,a.deliver_agent_code                                 as deliver_agent_code
      ,a.deliver_agent_name                                 as deliver_agent_name
      ,a.deliver_city_id                                    as deliver_city_id
      ,a.deliver_city_desc                                  as deliver_city_desc
      ,a.deliver_provider_id                                as deliver_provider_id
      ,a.deliver_provider_desc                              as deliver_provider_desc
      ,a.deliver_time                                        as deliver_time
      ,a.sign_time                                          as sign_time
      ,a.is_reback                                          as is_reback
      ,a.first_center_network_code                          as first_center_network_code
      ,e.name                                               as first_center_network_name
      ,e.city_id                                            as first_center_city_id
      ,e.city_desc                                          as first_center_city_desc
      ,e.provider_id                                        as first_center_provider_id
      ,e.provider_desc                                      as first_center_provider_desc
      ,a.first_center_arrival_early_time                    as first_center_arrival_early_time
      ,a.second_center_network_code                         as second_center_network_code
      ,a.second_center_arrival_early_time                    as second_center_arrival_early_time
      ,a.end_center_network_code                            as end_center_network_code
      ,b.send_station_network_code                          as send_station_network_code
      ,b.send_station_network_name                          as send_station_network_name
      ,b.send_station_provider_id                           as send_station_provider_id
      ,b.send_station_provider_desc                         as send_station_provider_desc
      ,b.send_station_city_id                               as send_station_city_id
      ,b.send_station_city_desc                             as send_station_city_desc
      ,b.send_station_network_type                          as send_station_network_type
      ,b.send_station_number                                as send_station_number
      ,b.new_send_station_number                            as new_send_station_number
      ,case when b.send_station_network_code=a.first_network_code and b.send_station_number='1' then coalesce(b.send_station_arrival_time,a.taking_early_scantime) else b.send_station_arrival_time            end send_station_arrival_time
      ,case when a.is_reback = '0' and b.send_station_network_code=a.end_network_code  and send_station_number<>'1' then coalesce(b.send_station_send_time,a.deliver_time) else b.send_station_send_time end send_station_send_time
      ,b.send_center_station_number
      ,b.next_station_network_code
      ,b.next_station_arrival_time
      ,b.next_station_send_time
      ,b.last_station_network_code
      ,b.last_station_arrival_time
      ,b.last_station_send_time
      ,b.next_next_station_network_code
      ,b.next_next_station_arrival_time
      ,b.next_next_station_send_time
      ,a.ordersource_code
      ,a.ordersource_name
      ,a.dt    as dt
from wide_unsign_summary_waybill_tab_thd                           a
left join trace_waybill_step_tab_thd                               b
  on a.waybill_no = b.waybill_no
left join jms_dim.dim_network_whole_massage                        c
  on a.first_network_code = c.code
left join jms_dim.dim_network_whole_massage                        d
  on a.end_network_code = d.code
left join jms_dim.dim_network_whole_massage                        e
  on a.first_center_network_code = e.code
),

---insert overwrite table bidefault.waybill_main_info_test2
waybill_main_info_test2 as (
select a.*
       ,concat('超过',b.timeout_hours,'小时')         as transit_timeout_desc
       ,b.timeout_hours                               as timeout_hours
  from waybill_main_info_test  a
  left join (select substr(start_province,1,2) as start_province,substr(sign_province,1,2) as sign_province,timeout_hours
               from jms_dim.dim_tab_false_ship_delivery_cfg
               ---from bidefault.tab_false_ship_delivery_cfg
               where state=1 and PLATFORM='桃花岛'
              group by substr(start_province,1,2),substr(sign_province,1,2),timeout_hours ) b
    on substr(a.first_provider_desc,1,2)=substr(b.start_province,1,2)           ----待优化首机构取值
   and substr(a.end_provider_desc,1,2)=substr(b.sign_province,1,2)
 where dt>=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}'
  and greatest(next_station_arrival_time,next_station_send_time)<deliver_time
),

-------------------判责处理
----1.首机构-始发中心超时
waybill_duty_info_1 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when greatest(next_station_arrival_time,next_station_send_time) is not null
              and (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then '始发网点-始发中心超时'
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is not null
                  and (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then '始发网点-始发中心超时'
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is null
              and (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then '始发网点-始发中心超时'
             else null
        end  as duty_type

       ,case when greatest(next_station_arrival_time,next_station_send_time) is not null
             and (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then transit_timeout_desc
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is not null
             and (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then transit_timeout_desc
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is null
             and (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when greatest(next_station_arrival_time,next_station_send_time) is not null
             and (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_code
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is not null
             and (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_code
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is null
             and (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_code
             else null
        end  as duty_network_code

       ,case when greatest(next_station_arrival_time,next_station_send_time) is not null
             and (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_name
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is not null
             and (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_name
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is null
             and (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then send_station_network_name
             else null
        end  as duty_network_name

       ,case when greatest(next_station_arrival_time,next_station_send_time) is not null
             and (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then (unix_timestamp(greatest(next_station_arrival_time,next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is not null
             and (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then (unix_timestamp(greatest(next_next_station_arrival_time,next_next_station_send_time)) - unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600
             when greatest(next_station_arrival_time,next_station_send_time) is null and greatest(next_next_station_arrival_time,next_next_station_send_time) is null
             and (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600 >timeout_hours
             then (unix_timestamp(current_timestamp())-unix_timestamp(least(send_station_send_time,send_station_arrival_time))) /3600
             else null
        end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_code=first_network_code and send_station_number = '1'  ----始发网点
  and next_station_network_code=first_center_network_code
---  and waybill_no='JT5224156193626'
),

----2.中心停留超时  （中转机构停留超时）
waybill_duty_info_2 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when send_station_network_code=first_center_network_code and send_center_station_number='1'
              and send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24    then '始发中心停留超时'
             when send_station_network_code=first_center_network_code and send_center_station_number='1'
              and send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
              and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24    then '始发中心停留超时'

             when send_station_network_code<>first_center_network_code and send_station_network_code<>end_center_network_code
              and send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24  then '中转机构停留超时'
             when send_station_network_code<>first_center_network_code and send_station_network_code<>end_center_network_code
              and send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
              and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24  then '中转机构停留超时'

             when send_station_network_code=end_center_network_code
              and send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24  then '末端中心超时'
             when send_station_network_code=end_center_network_code
              and send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
              and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24  then '末端中心超时'
             else null
        end  as duty_type
      ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then concat('超过',24,'小时')
            when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')='' and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then concat('超过',24,'小时')
            else null
       end  as duty_standard

      ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then send_station_network_code
            when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')='' and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then send_station_network_code
            else null
       end  as duty_network_code

      ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then send_station_network_name
            when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')='' and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24
            then send_station_network_name
            else null
       end  as duty_network_name

     ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >24
           then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600
           when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')='' and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 >24
           then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) /3600
           else null
       end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_type='2'   ---中心
),

----3.末端网点停留超时
waybill_duty_info_3 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >timeout_hours then '末端网点停留超时'
             when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
             and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 > timeout_hours  then '末端网点停留超时'
             else null
        end  as duty_type
       ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >timeout_hours then transit_timeout_desc
             when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
             and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 > timeout_hours  then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >timeout_hours then send_station_network_code
             when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
             and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 > timeout_hours  then send_station_network_code
             else null
        end  as duty_network_code

       ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >timeout_hours then send_station_network_name
             when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
             and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 > timeout_hours  then send_station_network_name
             else null
        end  as duty_network_name

       ,case when send_station_send_time is not null and (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600 >timeout_hours
             then (unix_timestamp(send_station_send_time)-unix_timestamp(send_station_arrival_time)) / 3600
             when send_station_send_time is null and coalesce(next_station_arrival_time,next_station_send_time,'')=''
             and (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600 > timeout_hours
             then (unix_timestamp(current_timestamp())-unix_timestamp(send_station_arrival_time)) / 3600
             else null
        end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_code=end_network_code and send_station_number <> '1'
),

----4.环节操作超时

waybill_duty_info_4 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '始发中心-中转机构'
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '始发中心-中转机构'
             else null
        end  as duty_type

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             else null
        end  as duty_network_code

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             else null
        end  as duty_network_name

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             else null
        end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_code=first_center_network_code and send_center_station_number=1
--- and waybill_no='JT5225929177487'
),

----5.中转环节流转超时

waybill_duty_info_5 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '中转环节流转超时'
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '中转环节流转超时'
             else null
        end  as duty_type

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             else null
        end  as duty_network_code

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             else null
        end  as duty_network_name

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             else null
        end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_code<>first_center_network_code and send_station_network_code<>end_center_network_code and  send_station_number>1
  and send_station_network_code <> end_network_code ---不为末端网点
--  and waybill_no='JT5225929177487'
),

----6.末端环节流转超时
waybill_duty_info_6 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,send_station_network_type
       ,send_station_arrival_time
       ,send_station_send_time
       ,send_station_number
       ,next_station_network_code
       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '末端环节流转超时'
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then '末端环节流转超时'
             else null
        end  as duty_type

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then transit_timeout_desc
             else null
        end  as duty_standard

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_code
             else null
        end  as duty_network_code

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then send_station_network_name
             else null
        end  as duty_network_name

       ,case when next_station_arrival_time is not null and next_station_send_time is not null
              and (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(least(next_station_arrival_time,next_station_send_time)) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             when next_station_arrival_time is null and next_station_send_time is null
              and (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600 > timeout_hours
             then (unix_timestamp(current_timestamp()) - unix_timestamp(greatest(send_station_arrival_time,send_station_send_time)))/3600
             else null
        end  as duration
       ,dt
 from waybill_main_info_test2
where send_station_network_code=end_center_network_code and send_center_station_number>1
),

----end—duty----

waybill_duty_info_tab1 as (
select a.*
       ,d.network_type
       ,d.agent_code
       ,d.agent_name
from (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,split((regexp_replace(duty_type,'\\d+\:','')),',')[0]           as first_duty_type
       ,split((regexp_replace(duty_standard,'\\d+\a','')),',')[0]       as first_duty_standard
       ,split((regexp_replace(duty_network_code,'\\d+\:','')),',')[0]   as first_duty_network_code
       ,split((regexp_replace(duty_network_name,'\\d+\:','')),',')[0]   as first_duty_network_name
       ,duty_network_code
       ,duty_network_name
       ,regexp_replace(duration,'\\d+\:','')  as duration
       ,dt
  from (
select waybill_no                                                             as waybill_no
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_code))) as send_station_network_code
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_name))) as send_station_network_name
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_type))) as send_station_network_type
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_arrival_time))) as send_station_arrival_time
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_send_time)))    as send_station_send_time
       ,concat_ws(',',sort_array(collect_set(tbb.duty_type)))                 as duty_type
       ,concat_ws(',',sort_array(collect_set(tbb.duty_standard)))             as duty_standard
       ,concat_ws(',',sort_array(collect_set(tbb.duty_network_code)))         as duty_network_code
       ,concat_ws(',',sort_array(collect_set(tbb.duty_network_name)))         as duty_network_name
       ,concat_ws(',',sort_array(collect_set(tbb.duration)))                  as duration
       ,dt
from (
    select waybill_no,dt
           ,concat(send_station_number,':',send_station_network_code)  as send_station_network_code
           ,concat(send_station_number,':',send_station_network_name)  as send_station_network_name
           ,concat(send_station_number,':',send_station_network_type)  as send_station_network_type
           ,concat(send_station_number,':',send_station_arrival_time)  as send_station_arrival_time
           ,concat(send_station_number,':',send_station_send_time)     as send_station_send_time
           ,concat(send_station_number,':',next_station_network_code)  as next_station_network_code
           ,concat(send_station_number,':',duty_type)                  as duty_type
           ,concat(send_station_number,'a',duty_standard)              as duty_standard
           ,concat(send_station_number,':',duty_network_code)          as duty_network_code
           ,concat(send_station_number,':',duty_network_name)          as duty_network_name
           ,concat(send_station_number,':',ROUND(duration,2))                   as duration
      from (
       select * from waybill_duty_info_1 where duty_network_code is not null and duty_type is not null
       union all
       select * from waybill_duty_info_2 where duty_network_code is not null and duty_type is not null
       union all
       select * from waybill_duty_info_3 where duty_network_code is not null and duty_type is not null
       union all
       select * from waybill_duty_info_4 where duty_network_code is not null and duty_type is not null
       union all
       select * from waybill_duty_info_5 where duty_network_code is not null and duty_type is not null
       union all
       select * from waybill_duty_info_6 where duty_network_code is not null and duty_type is not null
       ) aaa
       ) tbb
group by tbb.waybill_no,dt
) tt
) a
left join jms_dim.dim_network_whole_massage d
on a.first_duty_network_code = d.code
),

waybill_link_info_tab1 as (
select waybill_no
       ,send_station_network_code
       ,send_station_network_name
       ,dt
  from (
select waybill_no                                                             as waybill_no
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_code))) as send_station_network_code
       ,concat_ws(',',sort_array(collect_set(tbb.send_station_network_name))) as send_station_network_name

       ,dt
from (
    select waybill_no,dt
           ,concat(new_send_station_number,':',send_station_network_code)  as send_station_network_code
           ,concat(new_send_station_number,':',send_station_network_name)  as send_station_network_name
      from waybill_main_info_test2
      ) tbb
group by tbb.waybill_no,dt
) tt
)

insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_mid partition (dt,ordersource_code)
select  a.waybill_no                       as bill_code
       ,a.input_time                       as input_time
       ,a.taking_scantime                  as taking_time
       ,b.first_duty_network_code          as first_duty_network_code
       ,b.first_duty_network_name          as first_duty_network_name
       ,b.network_type                     as network_type
       ,b.agent_code                       as agent_code
       ,b.agent_name                       as agent_name
       ,b.first_duty_type                  as first_duty_type
       ,b.first_duty_standard              as first_duty_standard
       ,a.first_network_code               as start_network_code
       ,a.first_network_name               as start_network_name
       ,a.first_agent_code                 as start_agent_code
       ,a.first_agent_name                 as start_agent_name
       ,a.first_city_id                    as start_city_id
       ,a.first_city_desc                  as start_city_desc
       ,a.first_provider_id                as start_provider_id
       ,a.first_provider_desc              as start_provider_desc
       ,a.end_network_code                 as destination_network_code
       ,a.end_network_name                 as destination_network_name
       ,a.end_agent_code                   as destination_agent_code
       ,a.end_agent_name                   as destination_agent_name
       ,a.end_city_id                      as destination_city_id
       ,a.end_city_desc                    as destination_city_desc
       ,a.end_provider_id                  as destination_provider_id
       ,a.end_provider_desc                as destination_provider_desc
       ,c.send_station_network_code        as line_link_code
       ,c.send_station_network_name        as line_link_name
       ,b.duty_network_code                as duty_org_code
       ,b.duty_network_name                as duty_org_name
       ,b.duration                         as duration
       ,a.deliver_network_code             as deliver_network_code
       ,a.deliver_network_name             as deliver_network_name
       ,a.deliver_city_id                  as deliver_city_id
       ,a.deliver_city_desc                as deliver_city_desc
       ,a.deliver_provider_id              as deliver_provider_id
       ,a.deliver_provider_desc            as deliver_provider_desc
       ,a.sign_time                        as sign_time
       ,a.is_reback                        as is_reback
       ,a.send_station_network_code        as fst_network_code
       ,a.send_station_network_name        as fst_network_name
       ,a.send_station_arrival_time        as fst_network_opt_time
       ,ordersource_name	               as ordersource_name
       ,to_date(a.taking_scantime)         as dt
       ,ordersource_code	               as ordersource_code
  from (select * from waybill_main_info_test2 where send_station_number=1
           and to_date(taking_scantime)>=date_sub('{{ execution_date | cst_ds }}',15) and to_date(taking_scantime)<='{{ execution_date | cst_ds }}'
        )  a
  inner join (select * from waybill_duty_info_tab1  where first_duty_type  is not null )  b
    on a.waybill_no = b.waybill_no
   and a.dt=b.dt
  left join waybill_link_info_tab1      c
    on a.waybill_no = c.waybill_no
   and a.dt=c.dt
distribute by dt
;